package org.dream.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.dream.domain.Daydeal;
import org.dream.domain.RisewidthResult;
import org.dream.util.LogFactory;
import org.dream.util.UUID;

public class DaydealPersistenceImpl implements DayDealDAO {
	public List<Daydeal> findDayDealsByCode(String code) {
		return HibernateSessionFactory.getSession().createQuery("from Daydeal where code =:code order by day")
				.setParameter("code", code).list();
	}

	public Double findSZPoint(String day, String code) {
		try {
			String sql = "select CLOSE from daydeal where code='" + code + "' and day='" + day + "'";
			Statement stmt = DBUtil.getStatement();
			LogFactory.getLog().logSql(sql);
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				return rs.getDouble("CLOSE");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<RisewidthResult> calRisewidthResult(Double riseWidth) {
		List<RisewidthResult> list = new ArrayList<RisewidthResult>();
		try {
			String sql = "select day,COUNT(code) as js,COUNT( CASE WHEN riseWidth>"
					+ riseWidth
					+ " and  riseWidth<20 THEN 1 ELSE NULL END) as num from daydeal where day>='2000/01/04' and (code like '60%' or code like '00%')  group by day order by day";
			Statement stmt = DBUtil.getStatement();
			LogFactory.getLog().logSql(sql);
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				RisewidthResult ztStockNum = new RisewidthResult();
				ztStockNum.setStatisticsDay(rs.getString("day"));
				ztStockNum.setADVANCE(rs.getInt("num"));
				ztStockNum.setJs(rs.getInt("js"));
				ztStockNum.setStatisticsWidth(riseWidth);
				list.add(ztStockNum);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 得到最新的股票
	 */
	public Daydeal findRecentlyDaydealMap(String code) {
		try {

			Statement stmt = DBUtil.getStatement();
			ResultSet rs = stmt
					.executeQuery("select top 1 *  from daydeal where code='" + code + "' order by day desc");
			if (rs.next()) {
				Daydeal daydeal = new Daydeal();
				daydeal.setId(rs.getString("id"));
				daydeal.setCode(rs.getString("code"));
				daydeal.setName(rs.getString("name"));
				daydeal.setDAY(rs.getString("day"));
				daydeal.setOPEN(rs.getDouble("OPEN"));
				daydeal.setCLOSE(rs.getDouble("CLOSE"));
				daydeal.setHIGH(rs.getDouble("HIGH"));
				daydeal.setLOW(rs.getDouble("LOW"));
				daydeal.setVOL(rs.getDouble("VOL"));
				daydeal.setRiseWidth(rs.getDouble("riseWidth"));
				daydeal.setVOLRiseWidth(rs.getDouble("VOLRiseWidth"));
				daydeal.setDayNum(rs.getInt("dayNum"));
				return daydeal;

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public void saveDaydeals(List<Daydeal> daydeals) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = null;
		String sql = "insert into daydeal(id,code,name,day,[OPEN],HIGH,LOW,[CLOSE],VOL,riseWidth,"
				+ "VOLRiseWidth,dayNum)values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (Daydeal daydeal : daydeals) {
				ps.setString(1, UUID.getUUID());
				ps.setString(2, daydeal.getCode());
				ps.setString(3, daydeal.getName());
				ps.setString(4, daydeal.getDAY());
				ps.setDouble(5, daydeal.getOPEN());
				ps.setDouble(6, daydeal.getHIGH());
				ps.setDouble(7, daydeal.getLOW());
				ps.setDouble(8, daydeal.getCLOSE());
				ps.setDouble(9, daydeal.getVOL());
				ps.setDouble(10, daydeal.getRiseWidth());
				ps.setDouble(11, daydeal.getVOLRiseWidth());
				ps.setDouble(12, daydeal.getDayNum());
				ps.addBatch();
			}
			ps.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);

			ps = conn.prepareStatement(sql);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			DBUtil.closeConnection(conn);
		}

	}

	public void clearDaydeals() {
		Statement stmt = DBUtil.getStatement();
		String sql = "drop table daydeal";
		try {
			stmt.execute(sql);
			sql = "create table daydeal (id char(32) not null,code varchar(10) not null, name varchar(100) not null,"
					+ "DAY  varchar(19) not null,\"OPEN\" float  null,HIGH float null,LOW float null,"
					+ "\"CLOSE\" float null,VOL float null,riseWidth float null,dayNum int null,"
					+ "VOLRiseWidth   float  null,constraint PK_DAYDEAL primary key  (id), constraint AK_CODE_DATA_DAYDEAL unique (code, DAY))";
			LogFactory.getLog().logSql(sql);
			stmt.execute(sql);
			sql = "delete from risewidthResult";
			stmt.execute(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Daydeal> findOverZf(String day, double zf) {
		return HibernateSessionFactory
				.getSession()
				.createQuery(
						"from Daydeal where (code like '60%' or code like '00%' ) and  day =:day and riseWidth>:riseWidth and riseWidth<30 order by riseWidth desc")
				.setParameter("day", day).setParameter("riseWidth", zf).list();
	}

	public List<Daydeal> findAfterDaydeal(String day, String code) {
		return HibernateSessionFactory.getSession()
				.createQuery("from Daydeal where   day >:day and code=:code order by day asc").setParameter("day", day)
				.setParameter("code", code).list();
	}
}
